#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == '' ]];
then dt=`date -d '-1 day' "+%Y-%m-%d"`
else dt=$1
fi

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into zx_dws.dws_intention_daycount
with tmp as (
select
    id,
    substring (create_date_time,1,10) as create_date_time,
    deleted,
    origin_type,
    itcast_subject_id,
    itcast_school_id,
    origin_channel,
    creator,
    school_name,
    subject_name,
    tdepart_id,
    dept_name,
    area,
    clus_id,
    clue_state,
    appeal_status
from hive.zx_dwb.dwb_intention_detail)
select
    create_date_time as date_time,
     case origin_type
            when 'NETSERVICE'
            then 'online'
            when 'PRESIGNUP'
            then 'online'
            else 'offline'
            end as origin_type, --线上线下
    itcast_school_id, -- 校区id
    school_name,  -- 校区名称
    itcast_subject_id, --学科id
    subject_name,  -- 学科名称
    origin_channel, --来源渠道
    tdepart_id, -- 部门id
    cast (dept_name as varchar) as dept_name, -- 部门名称
    area, --地区
    case when clue_state = 'VALID_NEW_CLUES'
        then 'new'
        when clue_state = 'INVALID_PUBLIC_OLD_CLUE'
        then 'old'
        else 'other' end as clue_state, --新老学员

    case when grouping (area) = 0
            then 'area' -- 地区
         when grouping (itcast_subject_id) = 0
            then 'subject' -- 学科
         when grouping (itcast_school_id) = 0
            then 'school' -- 校区
         when grouping (origin_channel) = 0
            then 'origin' -- 渠道
         when grouping (tdepart_id) = 0
            then 'dept' -- 部门
         when grouping (create_date_time) = 0
            then 'all'
         else 'others' end as group_type, -- 分组标记

    -- 指标计算
    count(if(deleted != cast (1 as varchar), 1, null)) as intention_cnt,    -- 意向个数
    count(if(appeal_status = 2 , 1, null)) as valid_clue_cnt, -- 有效线索个数

    '${dt}' as dt
from tmp
group by
grouping sets (
    (create_date_time),
    (create_date_time,origin_type,clue_state),
    (create_date_time,area,origin_type,clue_state),
    (create_date_time,itcast_subject_id,subject_name,origin_type,clue_state),
    (create_date_time,itcast_school_id,school_name,origin_type,clue_state),
    (create_date_time,origin_channel,origin_type,clue_state),
    (create_date_time,tdepart_id,dept_name,origin_type,clue_state)
);
"